package com.dc.commons.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;

import com.dc.commons.procedure.pager.DcPagerResult;
import com.dc.commons.utils.Hibernates;

/*******************************************************************************<P>
 * 文件名: DcProcedureTemplate.java <br>
 * 版本: 1.0<br>
 * 描述: 结果集封装，包含 总的记录数<br>
 * 版权所有: <br>
 * <P>
 * 创建者: 王建栋 <br>
 * 创建日期: 2016年4月26日 上午10:15:28<br>
 * 修改者: <br>
 * 修改日期: <br>
 * 修改说明: <br>
 ******************************************************************************/
public class DcProcedureTemplate {
	private JdbcTemplate jdbcTemplate;
	private DcProcedureCallable dcProcedureCallable;
 
	 public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	 
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		this.dcProcedureCallable=Hibernates.getDcDataType(jdbcTemplate.getDataSource());
	}
	public DcProcedureCallable getDcProcedureCallable() {
		return dcProcedureCallable;
	}
	
	/**
	 * 执行存储过程，不返回任何结果【待验证】 //TODO
	 * @param dcCallableStatementCreator
	 * @param callback
	 * @param proc
	 * @return 单个对象
	 */
	public <T>T execute(final DcCallableStatementCreator dcCallableStatementCreator,final DcCallableStatementCallback<T> callback,final String proc) {
		 return jdbcTemplate.execute(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection connection)
					throws SQLException {
				
				CallableStatement callableStatement=dcProcedureCallable.prepareCall(connection, proc);
				return dcCallableStatementCreator.createCallableStatement(connection, callableStatement);
			}
		},null);
		
	 }
	
	/**
	 * 存储过程查询结果集。
	 * @param dcCallableStatementCreator
	 * @param callback
	 * @param proc
	 * @return
	 * 
	 * 
	 * 该方案主要是为了解决报表调用存储过程，Oracle，SqlServer 调用方式不统一，维护不方便。<br>
	 * 约定：<br>
	 *  存储过程名称 不需要提供输出游标的站位符， 输入参数从第二个开始<br>
	 * 	SqlServer存储过程   CREATE PROCEDURE [dbo].[getVdnLog] @vdnno varchar<br>
	 * 	Oracle 存储过程返回游标，第一个参数为游标 create or replace procedure getVdnLog(p_cusor  out sys_refcursor, p_vdnno   in varchar2)<br>
	 *  Mysql 需要在第一位设置一个没有实际用处的占位符合CREATE PROCEDURE `getVdnLog`(IN v_test VARCHAR(1),IN varning_type VARCHAR(10) )<br>
	 *  
	*/
	public <T>List<T> query(final DcCallableStatementCreator dcCallableStatementCreator,final DcCallableStatementCallback<List<T>> callback,final String proc) {
		 return jdbcTemplate.execute(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection connection)
					throws SQLException {
				
				CallableStatement callableStatement=dcProcedureCallable.prepareCall(connection, proc);
				dcProcedureCallable.registerOutCursor(callableStatement);
				return dcCallableStatementCreator.createCallableStatement(connection, callableStatement);
			}
		},new CallableStatementCallback<List<T>>() {
	
			@Override
			public List<T> doInCallableStatement(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				ResultSet rs=dcProcedureCallable.executeQuery(callableStatement);
				return callback.doInCallableStatement(callableStatement, rs,0);
			}
		});
		
	 }
	 
	/**
	 * 通过存储过程分页查询
	 * @param dcCallableStatementCreator
	 * @param callback
	 * @param proc
	 * @return
	 * 
	 * 该方案主要是为了解决报表调用存储过程，Oracle，SqlServer 调用方式不统一，维护不方便。<br>
	 * 约定：<br>
	 *  存储过程名称 需要自己提供总量的输出占位符，不需要提供输出游标的站位符， 输入参数从第三个开始<br>
	 * 	SqlServer存储过程 第一个参数为输出总量 ALTER PROCEDURE [dbo].[getVdnLogPage] @count int output,	@vdnno varchar<br>
	 * 	Oracle 存储过程返回游标，第一个参为总量，第二个参数为游标 create or replace procedure getVdnLogPage(p_totalcount out integer,p_cusor  out sys_refcursor,p_code   in varchar2)<br>
	 *  Mysql 需要在第一位设置一个没有实际用处的占位符合,第二个参数为 输出的总量，CREATE  PROCEDURE `getVdnLogPage`(IN v_test VARCHAR(1),OUT v_count INT, warning_type VARCHAR(50))<br>
	 */
	public <T>Page<T> queryPager(final DcCallableStatementCreator dcCallableStatementCreator,final DcCallableStatementCallback<Page<T>> callback,final String proc) {
		 return jdbcTemplate.execute(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection connection)
					throws SQLException {
				
				CallableStatement callableStatement=dcProcedureCallable.preparePagerCall(connection, proc);
				dcProcedureCallable.registerPagerOutCursor(callableStatement);
				return dcCallableStatementCreator.createCallableStatement(connection, callableStatement);
			}
		},new CallableStatementCallback<Page<T>>() {
	
			@Override
			public Page<T> doInCallableStatement(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				DcPagerResult dcPagerResult=dcProcedureCallable.executePagerQuery(callableStatement);
				return (Page<T>)callback.doInCallableStatement(callableStatement, dcPagerResult.getResultSet(),dcPagerResult.getTotalCount());
			}
		});
		
	 }
}
